create or replace view vac_list_templates_4_1 as
with tl as
(
select
 workspace,
 workspace_display_name,
 application_id,
 application_name,
 template_name,
 ac_utils.hs(list_template_current)                     list_template_current_hs,
 dbms_lob.substr(list_template_current, 4000, 1)        list_template_current_sr1,
 dbms_lob.substr(list_template_current, 4000, 4000+1 )  list_template_current_sr2,
 dbms_lob.substr(list_template_current, 4000, 8000+1 )  list_template_current_sr3,
 dbms_lob.substr(list_template_current, 4000, 12000+1)  list_template_current_sr4,
 --
 ac_utils.hs(list_template_noncurrent)                  list_template_noncurrent_hs,
 dbms_lob.substr(list_template_current, 4000, 1)        list_template_noncurrent_sr1,
 dbms_lob.substr(list_template_current, 4000, 4000+1 )  list_template_noncurrent_sr2,
 dbms_lob.substr(list_template_current, 4000, 8000+1 )  list_template_noncurrent_sr3,
 dbms_lob.substr(list_template_current, 4000, 12000+1)  list_template_noncurrent_sr4,
 --
 list_template_before_rows,
 list_template_after_rows,
 between_items,
 before_sub_list,
 after_sub_list,
 between_sub_list_items,
 --
 ac_utils.hs(sub_list_item_current)                           sub_list_item_current_hs,
 dbms_lob.substr(sub_list_item_current, 4000, 1)              sub_list_item_current_sr1,
 dbms_lob.substr(sub_list_item_current, 4000, 4000+1 )        sub_list_item_current_sr2,
 dbms_lob.substr(sub_list_item_current, 4000, 8000+1 )        sub_list_item_current_sr3,
 dbms_lob.substr(sub_list_item_current, 4000, 12000+1)        sub_list_item_current_sr4,
 --
 ac_utils.hs(sub_list_item_noncurrent)                        sub_list_item_noncurrent_hs,
 dbms_lob.substr(sub_list_item_noncurrent, 4000, 1)           sub_list_item_noncurrent_sr1,
 dbms_lob.substr(sub_list_item_noncurrent, 4000, 4000+1 )     sub_list_item_noncurrent_sr2,
 dbms_lob.substr(sub_list_item_noncurrent, 4000, 8000+1 )     sub_list_item_noncurrent_sr3,
 dbms_lob.substr(sub_list_item_noncurrent, 4000, 12000+1)     sub_list_item_noncurrent_sr4,
 --
 to_char(item_template_curr_w_child)                          item_template_curr_w_child,
 to_char(item_template_noncurr_w_child)                       item_template_noncurr_w_child,
 --
 ac_utils.hs(sub_template_curr_w_child)                       sub_template_curr_w_child_hs,
 dbms_lob.substr(sub_template_curr_w_child, 4000, 1)          sub_template_curr_w_child_sr1,
 dbms_lob.substr(sub_template_curr_w_child, 4000, 4000+1 )    sub_template_curr_w_child_sr2,
 dbms_lob.substr(sub_template_curr_w_child, 4000, 8000+1 )    sub_template_curr_w_child_sr3,
 dbms_lob.substr(sub_template_curr_w_child, 4000, 12000+1)    sub_template_curr_w_child_sr4,
 --
 ac_utils.hs(sub_template_noncurr_w_child)                    sub_template_nc_w_child_hs,
 dbms_lob.substr(sub_template_noncurr_w_child, 4000, 1)       sub_template_nc_w_child_sr1,
 dbms_lob.substr(sub_template_noncurr_w_child, 4000, 4000+1 ) sub_template_nc_w_child_sr2,
 dbms_lob.substr(sub_template_noncurr_w_child, 4000, 8000+1 ) sub_template_nc_w_child_sr3,
 dbms_lob.substr(sub_template_noncurr_w_child, 4000, 12000+1) sub_template_nc_w_child_sr4,
 --
 first_list_template_noncurrent,
 first_list_template_current,
 f_item_template_curr_w_child,
 fitem_template_noncurr_w_child,
 is_subscribed,
 subscribed_from,
 last_updated_by,
 last_updated_on,
 theme_number,
 theme_class,
 translate_this_template,
 component_comment,
 list_template_id,
 component_signature
from apex_application_temp_list
)
select
 workspace,
 workspace_display_name,
 application_id,
 application_name,
 template_name,
 theme_number,
 u_name,
 u_value,
 src_view_name,
 --
 to_clob(list_template_current_sr1)       || to_clob(list_template_current_sr2)
 || to_clob(list_template_current_sr3)    || to_clob(list_template_current_sr4) list_template_current_sr,
 --
 to_clob(list_template_noncurrent_sr1)    || to_clob(list_template_noncurrent_sr2)
 || to_clob(list_template_noncurrent_sr3) || to_clob(list_template_noncurrent_sr4) list_template_noncurrent_sr,
 --
 to_clob(sub_list_item_current_sr1)       || to_clob(sub_list_item_current_sr2)
 || to_clob(sub_list_item_current_sr3)    || to_clob(sub_list_item_current_sr4) sub_list_item_current_sr,
 --
 to_clob(sub_list_item_noncurrent_sr1)    || to_clob(sub_list_item_noncurrent_sr2)
 || to_clob(sub_list_item_noncurrent_sr3) || to_clob(sub_list_item_noncurrent_sr4) sub_list_item_noncurrent_sr,
 --
 to_clob(sub_template_nc_w_child_sr1)     || to_clob(sub_template_nc_w_child_sr2)
 || to_clob(sub_template_nc_w_child_sr3)  || to_clob(sub_template_nc_w_child_sr4) sub_template_nc_w_child_sr,
 --
 to_clob(sub_template_curr_w_child_sr1)     || to_clob(sub_template_curr_w_child_sr2)
 || to_clob(sub_template_curr_w_child_sr3)  || to_clob(sub_template_curr_w_child_sr4) sub_template_curr_w_child_sr
 --
from
(
  select
   workspace,
   workspace_display_name,
   application_id,
   application_name,
   template_name,
   last_updated_by,
   last_updated_on,
   theme_number,
   theme_class,
--
   list_template_current_sr1,
   list_template_current_sr2,
   list_template_current_sr3,
   list_template_current_sr4,
--
   list_template_noncurrent_sr1,
   list_template_noncurrent_sr2,
   list_template_noncurrent_sr3,
   list_template_noncurrent_sr4,
--
   sub_list_item_current_sr1,
   sub_list_item_current_sr2,
   sub_list_item_current_sr3,
   sub_list_item_current_sr4,
 --
   sub_list_item_noncurrent_sr1,
   sub_list_item_noncurrent_sr2,
   sub_list_item_noncurrent_sr3,
   sub_list_item_noncurrent_sr4,
 --
   sub_template_nc_w_child_sr1,
   sub_template_nc_w_child_sr2,
   sub_template_nc_w_child_sr3,
   sub_template_nc_w_child_sr4,
 --
   sub_template_curr_w_child_sr1,
   sub_template_curr_w_child_sr2,
   sub_template_curr_w_child_sr3,
   sub_template_curr_w_child_sr4,
 --
   u_name,
   u_value,
   'APEX_APPLICATION_TEMP_LIST' src_view_name
  from tl
   UNPIVOT INCLUDE NULLS (
     u_value
     FOR    u_name
     IN
     (
       list_template_current_hs,
       list_template_noncurrent_hs,
       list_template_before_rows,
       list_template_after_rows,
       between_items,
       before_sub_list,
       after_sub_list,
       between_sub_list_items,
       sub_list_item_current_hs,
       sub_list_item_noncurrent_hs,
       item_template_curr_w_child,
       item_template_noncurr_w_child,
       sub_template_curr_w_child_hs,
       sub_template_nc_w_child_hs,
       first_list_template_noncurrent,
       first_list_template_current,
       f_item_template_curr_w_child,
       fitem_template_noncurr_w_child,
       is_subscribed,
       subscribed_from,
       translate_this_template,
       component_comment
     )
  )
);

